﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using System.Data;
using System.Data.SqlClient;
using Entities;

namespace DataAccessLayer
{
    public class VuDieuDAL : DBConnection
    {
        public List<VuDieuEntity> getAll()
        {        
            List<VuDieuEntity> lst = new List<VuDieuEntity>();
            try
            {
                MySqlCommand cmd = new MySqlCommand("vu_dieu_getAll", con);
                cmd.CommandType = CommandType.StoredProcedure;

                if (con.State != ConnectionState.Open)
                    con.Open();
                MySqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                while(dr.Read())
                {
                    VuDieuEntity obj = new VuDieuEntity();
                    obj.MaVuDieu = Convert.ToInt32(dr["MaVuDieu"]);
                    obj.TenVuDieu = Convert.ToString(dr["TenVuDieu"]);
                    obj.MoTa = Convert.ToString(dr["MoTa"]);
                    lst.Add(obj);
                }
                dr.Close();
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return lst;
        }

        public VuDieuEntity getById(int _id)
        {
            VuDieuEntity obj = new VuDieuEntity();
            try
            {
                MySqlCommand cmd = new MySqlCommand("vu_dieu_getById", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("maVuDieu", _id);

                if (con.State != ConnectionState.Open)
                    con.Open();
                MySqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                if (dr.Read())
                {
                    obj.MaVuDieu = Convert.ToInt32(dr["MaVuDieu"]);
                    obj.TenVuDieu = Convert.ToString(dr["TenVuDieu"]);
                    obj.MoTa = Convert.ToString(dr["MoTa"]);
                }
                dr.Close();
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return obj;
        }

        public int addVuDieu(VuDieuEntity _obj)
        {
            int result = 0;
            try
            {
                if (_obj.MaVuDieu != -1)
                    throw new Exception("Vu Dieu Da Ton Tai");

                MySqlCommand cmd = new MySqlCommand("vu_dieu_insert", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("tenVuDieu", _obj.TenVuDieu);
                cmd.Parameters.AddWithValue("moTa", _obj.MoTa);
                cmd.Parameters.AddWithValue("lastId", MySqlDbType.Int32);
                cmd.Parameters["lastId"].Direction = ParameterDirection.Output;

                if (con.State != ConnectionState.Open)
                    con.Open();
                result = cmd.ExecuteNonQuery();
                int i = Convert.ToInt32(cmd.Parameters["lastId"].Value);
                if(_obj.MaVuDieu==-1)
                    _obj.MaVuDieu = i;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return result;
        }

        public int deleteVuDieu(int _id)
        {
            int result = 0;
            try
            {
                if (_id == -1)
                    return 0;
                MySqlCommand cmd = new MySqlCommand("vu_dieu_delete", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("maVuDieu", _id);
                cmd.Parameters["maVuDieu"].Direction = ParameterDirection.Input;

                if (con.State != ConnectionState.Open)
                    con.Open();
                result = cmd.ExecuteNonQuery();
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return result;
        }

        public int updateDieuNhay(VuDieuEntity _obj)
        {
            int result = 0;
            try
            {
                if (_obj.MaVuDieu == -1)
                    return addVuDieu(_obj);

                MySqlCommand cmd = new MySqlCommand("vu_dieu_update", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("maVuDieu", _obj.MaVuDieu);
                cmd.Parameters.AddWithValue("tenVuDieu", _obj.TenVuDieu);
                cmd.Parameters.AddWithValue("moTa", _obj.MoTa);

                if (con.State != ConnectionState.Open)
                    con.Open();
                result = cmd.ExecuteNonQuery();
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return result;
        }
    }
}
